﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Helper;
using CallCenter.Data.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using iTextSharp.text.pdf;
using iTextSharp.text;
using System.IO;

namespace CallCenter.Data.BizUtilities
{
    public class InvoiceUtility : BaseUtility
    {
        public InvoiceUtility(DataManager manager)
            : base(manager)
        { }


        public ReturnMessage<InvoiceVM> InsertToInvoice(InvoiceVM model)
        {
            var returnMessage = new ReturnMessage<InvoiceVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {          
            DataTable dtProdReq = this.Bridge.ProductRequestUtility.GetProductRequest(model.productRequestId);
            int contactId = 0;

            DataTable dtContact = this.Bridge.CommunicationContacts.GetContacts(model.companyId);

            #region insert current  user as assigined employee by default
            DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.userId);
            int employeeId = Convert.ToInt32(empDr["EmployeeId"].ToString());
            #endregion

          
            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
         



            var id = this.Insert(model.userId, model.companyId, model.productRequestId, contactId, employeeId, model.invoiceDate, model.description, 1, model.term, model.notes);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;

                //Company Contacts iteration
                List<EmailVM> emailVmList = new List<EmailVM>();
                List<EmailVM> companycontList = new List<EmailVM>();
                if (!string.IsNullOrEmpty(model.InvcompanyContactIds))
                {
                    foreach (var item in model.InvcompanyContactIds.Split(','))
                    {
                        int commId = id;
                        int comContId = Convert.ToInt32(item);
                        //if (this.Bridge.CompanyContactsUtility.Insert(model.companyId, comContId) <= 0)
                        //this.Bridge.CompanyContactsUtility.InsertToComContact(commId, comContId, DateTime.Now);
                        this.InsertInvoiceContact(commId, comContId, DateTime.Now);
                        //get email id for the contacts to send the email.
                        List<EmailVM> emailTo = this.Bridge.CommunicationContacts.GetContactEmails(comContId);
                        emailVmList.AddRange(emailTo);
                    }
                }
                string ScheduleDate = "";
                ScheduleDate = GetScheduleDateByTerm(model.term);
                if (ScheduleDate != "")
                    UpdateSchedulerDateByinvoiceId(model.id, ScheduleDate);
                returnMessage.Message = "Record has been inserted";
                try
                {
                    List<string> AllContactList = emailVmList.Where(e => e.email != null && e.email != "").Select(e => e.email).Distinct().ToList();
                    string[] to = AllContactList.ToArray();
                    string[] cc = { model.emailTo };
                    WebMail.SendMail(to, cc, "", model.notes, null);
                    returnMessage.Message += "and Mails were sended to corresponding contacts";
                    returnMessage.Status = true;
                }
                catch
                {
                    returnMessage.Message += " But Some of the contact doesn't have an email address on file!!";
                }
                returnMessage.Message = "Record inserted to database";
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        private void InsertInvoiceContact(int commId, int comContId, DateTime dateTime)
        {
            int nRtn = -1;

            string sql = "Insert INTO ccInvoiceContact " +
                 "(Invoiceid, contactId, date) VALUES(@commId, @contactId, @date)  SELECT @pResult =@@IDENTITY ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, comContId);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.SmallDateTime, 10, dateTime);
                {
                    var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                    param.Direction = System.Data.ParameterDirection.Output;

                    this.Factory.ExecuteNonQuery(cmd);
                    nRtn = Convert.ToInt32(param.Value);
                }
            }
        }


        public ReturnMessage<InvoiceVM> UpdateToInvoice(InvoiceVM model)
        {
            var returnMessage = new ReturnMessage<InvoiceVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {

            DataTable dtProdReq = this.Bridge.ProductRequestUtility.GetProductRequest(model.productRequestId);
            int contactId = 0;
           
            #region insert current  user as assigined employee by default
            DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(model.userId);
            int employeeId = Convert.ToInt32(empDr["EmployeeId"].ToString());
            #endregion


            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
           


         





            var id = this.Update(model.id, model.userId, model.companyId, model.productRequestId, contactId, employeeId, model.invoiceDate, model.description, 1, model.term, model.notes);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                //Company Contacts iteration
                List<EmailVM> emailVmList = new List<EmailVM>();
                List<EmailVM> companycontList = new List<EmailVM>();
                if (!string.IsNullOrEmpty(model.InvcompanyContactIds))
                {
                    this.DeleteInvoiceContactByInvoiceID(model.id);
                    foreach (var item in model.InvcompanyContactIds.Split(','))
                    {
                        int commId = id;
                        int comContId = Convert.ToInt32(item);
                        //if (this.Bridge.CompanyContactsUtility.Insert(model.companyId, comContId) <= 0)
                        //this.Bridge.CompanyContactsUtility.InsertToComContact(commId, comContId, DateTime.Now);
                        this.InsertInvoiceContact(commId, comContId, DateTime.Now);
                        //get email id for the contacts to send the email.
                        List<EmailVM> emailTo = this.Bridge.CommunicationContacts.GetContactEmails(comContId);
                        emailVmList.AddRange(emailTo);
                    }
                }
                string ScheduleDate = "";
                ScheduleDate = GetScheduleDateByTerm(model.term);
                if (ScheduleDate != "")
                    UpdateSchedulerDateByinvoiceId(model.id, ScheduleDate);
                returnMessage.Message = "Record has been inserted";
                try
                {
                    List<string> AllContactList = emailVmList.Where(e => e.email != null && e.email != "").Select(e => e.email).Distinct().ToList();
                    string[] to = AllContactList.ToArray();
                    string[] cc = { model.emailTo };
                    WebMail.SendMail(to, cc, "", model.notes, null);
                    returnMessage.Message += "and Mails were sended to corresponding contacts";
                    returnMessage.Status = true;
                }
                catch
                {
                    returnMessage.Message += " But Some of the contact doesn't have an email address on file!!";
                }
                returnMessage.Message = "Record inserted to database";
                returnMessage.Status = true;

            }
            return returnMessage;
        }

        private string GetScheduleDateByTerm(string ScheduleTerm)
        {
            DateTime baseDate = DateTime.Today;
            string ScheduleDate = "";
            if (ScheduleTerm == "Weekly")
            {
                DateTime thisWeekStart = baseDate.AddDays(-(int)baseDate.DayOfWeek);
                ScheduleDate = thisWeekStart.AddDays(8).ToString();
            }
            else if (ScheduleTerm == "Bi-Weekly")
            {
                DateTime MiddleDate = new DateTime(baseDate.Year, baseDate.Month, 15);
                if (MiddleDate >= baseDate)
                    ScheduleDate = MiddleDate.ToString();
                else
                {
                    var thisMonthStart = baseDate.AddDays(1 - baseDate.Day);
                    ScheduleDate = thisMonthStart.AddMonths(1).ToString();
                }
            }
            else if (ScheduleTerm == "Monthly")
            {
                var thisMonthStart = baseDate.AddDays(1 - baseDate.Day);
                ScheduleDate = thisMonthStart.AddMonths(1).ToString();
            }
            else if (ScheduleTerm == "Quaterly")
            {
                int quarterNumber = (baseDate.Month - 1) / 3 + 1;
                DateTime firstDayOfQuarter = new DateTime(baseDate.Year, (quarterNumber - 1) * 3 + 1, 1);
                ScheduleDate = firstDayOfQuarter.AddMonths(3).ToString();
                //var thisMonthStart = baseDate.AddDays(1 - baseDate.Day);
                //ScheduleDate = thisMonthStart.AddMonths(3).AddSeconds(-1).AddDays(1).ToString();
            }
            else if (ScheduleTerm == "Six Months")
            {
                if (DateTime.Now.Month < 6)
                {
                    DateTime NextYear = new DateTime(baseDate.Year, 6, 1);
                    ScheduleDate = NextYear.ToString();
                }
                else
                {
                    DateTime NextYear = new DateTime(baseDate.Year + 1, 1, 1);
                    ScheduleDate = NextYear.ToString();
                }
            }
            else if (ScheduleTerm == "Yearly")
            {
                DateTime NextYear = new DateTime(baseDate.Year + 1, 1, 1);
                ScheduleDate = NextYear.ToString();
            }
            return ScheduleDate;
        }

        private void DeleteInvoiceContactByInvoiceID(int invoiceid)
        {
            string sql = "DELETE FROM ccInvoiceContact where Invoiceid =" + invoiceid;
            this.Factory.ExecuteTable(sql);
        }


        //assign placeholder for the values in the table to insert
        public int Insert(
            int userId,
            int companyId,
            int productRequestId,
            int contactId,
            int employeeId,
            Nullable<DateTime> invoiceDate,
            string description,
            int period,
            string term,
            string notes
            )
        {
            int nRtn = -1;
            string sql = "INSERT INTO ccInvoice" +
                 "(userId, productRequestId, companyId, contactId, employeeId, invoiceDate, description, period, term, notes)" +
             " VALUES(@userId, @productRequestId, @companyId, @contactId, @employeeId, @invoiceDate, @description, @period, @term, @notes)" +
             " SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 10, productRequestId);
                this.Factory.AddParameter(cmd, "@employeeId", SqlDbType.Int, 10, employeeId);
              //  this.Factory.AddParameter(cmd, "@invoiceDate", SqlDbType.SmallDateTime, 10, invoiceDate);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 10, description);
                this.Factory.AddParameter(cmd, "@period", SqlDbType.Int, 10, period);
                this.Factory.AddParameter(cmd, "@term", SqlDbType.NVarChar, 10, term);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);

                if ((invoiceDate == null) || (invoiceDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@invoiceDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@invoiceDate", SqlDbType.DateTime, 10, invoiceDate);
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }

        public int Update(
            int id,
           int userId,
           int companyId,
           int productRequestId,
           int contactId,
           int employeeId,
           Nullable<DateTime> invoiceDate,
           string description,
             int period,
            string term,
            string notes
           )
        {
            int nRtn = -1;
            string sql = "UPDATE ccInvoice " +
                 " SET userId=@userId, productRequestId=@productRequestId, companyId=@companyId, " +
                 " contactId=@contactId, employeeId=@employeeId, invoiceDate=@invoiceDate, description=@description, " +
                 " period=@period, term=@term, notes=@notes WHERE id=@ID ";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@userId", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 10, productRequestId);
                this.Factory.AddParameter(cmd, "@employeeId", SqlDbType.Int, 10, employeeId);
               // this.Factory.AddParameter(cmd, "@invoiceDate", SqlDbType.SmallDateTime, 10, invoiceDate);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 10, description);
                this.Factory.AddParameter(cmd, "@period", SqlDbType.Int, 10, period);
                this.Factory.AddParameter(cmd, "@term", SqlDbType.NVarChar, 10, term);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);


                if ((invoiceDate == null) || (invoiceDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@invoiceDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@invoiceDate", SqlDbType.DateTime, 10, invoiceDate);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }


        public void Delete(int nInvoice) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ccInvoiceContact " +
                                      "WHERE InvoiceId =  '" + nInvoice+"' DELETE FROM ccInvoice " +
                  "WHERE  ID =  '" + nInvoice + "'";

          
                this.Factory.ExecuteNonQuery(sql);
                  

        }

        public void DeleteAll() //delete all the rows in the data.
        {
            string sql = "DELETE FROM ccInvoice";
            this.Factory.ExecuteTable(sql);
        }

        public List<InvoiceVM> GetAll()
        {
            string sql = "SELECT * FROM ccInvoice";

            return this.Factory.ExecuteObjects<InvoiceVM>(sql);
        }

        public DataTable GetAll(int invceId)
        {
            string sql = "SELECT * FROM ccInvoice where id=@ID";



            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, invceId);
            }
            return this.Factory.ExecuteTable(sql);
        }

        public List<CompanyGridViewModel> GetAllCompanies()
        {

            string sql = "select id, name as compName from ddlcompany";


            return this.Factory.ExecuteObjects<CompanyGridViewModel>(sql);

        }
        public List<InvoiceVM> GetAllGrid()
        {
            //string sql = "SELECT i.*, (SELECT TOP 1 lastName+', '+firstName as empName FROM ddlEmployees e " +
            //    " INNER JOIN ccContacts on ccContacts.id = e.contactId WHERE ccContacts.id = i.employeeId) as empName, " +
            //    " cp.product as productName, c.name as compName, cc.lastName+', '+cc.firstName as clientContName  from ccInvoice i " +
            //    " INNER JOIN ddlProductRequests pr on pr.id= i.productRequestId " +
            //    " INNER JOIN ddlProducts cp on cp.id = pr.productId " +
            //    " INNER JOIN ddlCompany c on c.id = i.companyId " +
            //    " LEFT JOIN ddlEmployees e on e.contactId = i.employeeId " +
            //    " LEFT JOIN ccContacts cc on cc.id = i.contactId ";



            string sql = "SELECT i.*, (SELECT TOP 1 lastName + ', ' + firstName as empName FROM ddlEmployees e " +
                    " INNER JOIN ccContacts on ccContacts.id = e.contactId WHERE ccContacts.id = i.employeeId) as empName, " +
                " cp.product as productName, c.name as compName, cc.lastName + ', ' + cc.firstName as clientContName , " +

                                  " STUFF((SELECT ',' + CAST(contactId AS VARCHAR(MAX))[text()] " +
                            " FROM ddlCompanyContacts WHERE contactId = i.contactId " +
                   " FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') AS CompanyContactIds," +

               "  STUFF((SELECT ', ' + CAST(cct.firstName AS VARCHAR(MAX)) + ' ' + CAST(cct.lastName AS VARCHAR(MAX))[text()] " +
               " FROM ddlCompanyContacts compcc INNER JOIN ccContacts cct ON cct.id = compcc.contactId WHERE compcc.contactId = i.contactId " +
                 " FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,' ') AS CompanyContacts " +

               "  from ccInvoice i " +
               "  INNER JOIN ddlProductRequests pr on pr.id = i.productRequestId " +
               "  INNER JOIN ddlProducts cp on cp.id = pr.productId " +
              "   INNER JOIN ddlCompany c on c.id = i.companyId " +
              "   LEFT JOIN ddlEmployees e on e.contactId = i.employeeId " +
               "  LEFT JOIN ccContacts cc on cc.id = i.contactId   WHERE c.id = i.contactId  ";

            return this.Factory.ExecuteObjects<InvoiceVM>(sql);


        }

        public List<InvoiceVM> GetAllGrid(int companyId)
        {

            string sql = "SELECT i.*, pr.name as productName, (SELECT TOP 1 lastName + ', ' + firstName as empName FROM ddlEmployees e " +
                  " INNER JOIN ccContacts on ccContacts.id = e.contactId WHERE ccContacts.id = i.employeeId) as empName, " +
              " c.name as compName, cc.lastName + ', ' + cc.firstName as clientContName , " +

                                " STUFF((SELECT ',' + CAST(contactId AS VARCHAR(MAX))[text()]  " +
                          " FROM ccInvoiceContact WHERE InvoiceId = i.id  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') " +
                 " AS InvcompanyContactIds,  " +

             "STUFF((SELECT ', ' + CAST(cct.firstName AS VARCHAR(MAX)) + ' ' + CAST(cct.lastName AS VARCHAR(MAX))[text()] " +
             " FROM ccInvoiceContact compcc INNER JOIN ccContacts cct ON cct.id = compcc.contactId " +
               " WHERE compcc.InvoiceId = i.id  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,' ') AS InvcompanyContacts  " +

             "  from ccInvoice i " +
             "  INNER JOIN ddlProductRequests pr on pr.id = i.productRequestId " +

            "   INNER JOIN ddlCompany c on c.id = i.companyId " +
            "   LEFT JOIN ddlEmployees e on e.contactId = i.employeeId " +
             "  LEFT JOIN ccContacts cc on cc.id = i.contactId    WHERE c.id = " + companyId;


            return this.Factory.ExecuteObjects<InvoiceVM>(sql);


        }
        public DataRow Invoic2PDF(int invoiceId)
        {

            string sql = " SELECT i.*, c.streetOne, c.streetTwo, c.city as compCity, " +
                                     " (SELECT TOP 1 state from ddlStates INNER JOIN ddlcompany on " +
                                     " ddlcompany.stateId = ddlStates.id) as compState, " +
                                     " (SELECT TOP 1 lastName+', '+firstName as empName FROM ddlEmployees e " +
                                     " INNER JOIN ccContacts on ccContacts.id = e.contactId " +
                                     " WHERE ccContacts.id = i.employeeId) as empName, " +
                                     " cp.product as productName, pr.name as projectname, c.name as compName, " +
                                     " cc.lastName+', '+cc.firstName as clientContName from ccInvoice i " +
                                     " INNER JOIN ddlProductRequests pr on pr.id= i.productRequestId " +
                                     " INNER JOIN ddlProducts cp on cp.id = pr.productId " +
                                     " INNER JOIN ddlCompany c on c.id = i.companyId " +
                                     " LEFT JOIN ddlEmployees e on e.contactId = i.employeeId " +
                                     " LEFT JOIN ccContacts cc on cc.id = i.contactId " +
                                     " WHERE i.id =" + invoiceId;
            return this.Factory.ExecuteRecord(sql);

            //da.Fill(dt);

            //if (dt.Rows.Count == 1)
            //    return dt.Rows[0];
            //else
            //    return null;


        }
        public DataTable GetContactId(int invoiceId)
        {
            string sql = "SELECT DISTINCT i.contactId FROM ccInvoice i " +
                                      "INNER JOIN ddlCompanyContacts ct on ct.companyId = i.companyId " +
                                      "where ct.emailInvoice = 'true' and i.id=" + invoiceId;

            return this.Factory.ExecuteTable(sql);

        }

        protected void sendEmails(int invceId)
        {
            #region contact id and employee Id from invocies to use to get email address of the contact

            DataTable dt = this.GetContactId(invceId);

            DataRow empDr = this.Bridge.EmployeeUtility.GetEmpIdByUser(24);
            int empId = Convert.ToInt32(empDr["EmployeeId"]);


            #endregion


         //  invceId = invoiceId;
            #region send email to a contact and cc email to the current user

            string ccEmail = this.Bridge.CommEmployeeUtility.GetEmpEmail(empId);
            // string file = generatePdf(invceId);
            //get email id for the contacts to send the email.
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int contactId = Convert.ToInt32(dt.Rows[i]["contactId"].ToString());

                    string emailTo = Convert.ToString(this.Bridge.CommunicationContacts.GetContEmail(contactId));
                    // string name = comCont.GetContEmailNames(contactId);

                    if (emailTo != "none")
                    {
                        string[] to = { emailTo };
                        string[] cc = { ccEmail };
                        //   WebMail.SendMail(to, cc, "Invoice", name + ",  Please find the attached invoice", file);
                        //Email.sendEmail(emailTo, subject, notes);
                    }
                }
            }
            else
            {
                string[] to = { "Return@someMail.com" };
                string[] cc = { ccEmail };
                // WebMail.SendMail(cc, to, "Invoice not sent!!", "Please check if the client has valid email address or client has atleast one email on file to send the invoice to.", file);
            }
            // generatePdf(invoiceId);

            #endregion


        }


        //protected void sendEmailBasedOnterm(int invceId)
        //{

        //}


        public void UpdateSchedulerDateByinvoiceId(int nInvoice, string ScheduleDate) //Update the Scheduler Date
        {
            string sql = "update ccInvoice set ScheduleDate = '" + ScheduleDate +
                 "' WHERE ID = " + nInvoice;
            this.Factory.ExecuteTable(sql);
        }


    }


}





